import mysql.connector
from mysql.connector import errorcode
from db_config import config


def connect_to_database():
    """连接到数据库"""
    try:
        conn = mysql.connector.connect(**config)
        return conn
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("错误的用户名和密码")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("数据库不存在，正在尝试创建")
            create_database()
            return connect_to_database()
        else:
            print(err)
        return None


def create_database():
    """创建数据库"""
    try:
        temp_config = {k: v for k, v in config.items() if k != 'database'}
        conn = mysql.connector.connect(**temp_config)
        cursor = conn.cursor()
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {config['database']}")
    finally:
        cursor.close()
        conn.close()


def create_table():
    """创建学生表"""
    conn = connect_to_database()
    if not conn:
        return

    cursor = conn.cursor()
    create_tab_sql = """
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        gender ENUM('男', '女') NOT NULL,
        age INT,
        score FLOAT,
        student_id VARCHAR(20) UNIQUE,
        class VARCHAR(50)
    )
    """
    cursor.execute(create_tab_sql)
    cursor.close()
    conn.close()


def insert_student(student):
    """插入学生数据"""
    conn = connect_to_database()
    if not conn:
        return

    cursor = conn.cursor()
    sql = """
    INSERT INTO students (name, gender, age, student_id, class, chinese_score, math_score, english_score)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """
    try:
        cursor.execute(sql, student)
        conn.commit()
        print("学生信息已添加")
    except mysql.connector.Error as e:
        print("插入失败")
    finally:
        cursor.close()
        conn.close()


def get_all_students():
    """获取所有学生信息,可指定排序方式"""
    conn = connect_to_database()
    if not conn:
        return
    cursor = conn.cursor(dictionary=True)
    sql = "SELECT * FROM students"
    cursor.execute(sql)
    results = cursor.fetchall()
    decoded_results = [
        {
            k: v.decode('utf-8') if isinstance(v, (bytearray, bytes)) else v
            for k, v in row.items()
        }
        for row in results
    ]
    cursor.close()
    conn.close()
    return decoded_results


def search_student(name):
    """根据姓名搜索学生"""
    conn = connect_to_database()
    if not conn:
        return
    cursor = conn.cursor(dictionary=True)
    sql = "SELECT * FROM students WHERE name like %s"
    cursor.execute(sql, (f"%{name}",))
    results = cursor.fetchall()
    decoded_results = [
        {
            k: v.decode('utf-8') if isinstance(v, (bytearray, bytes)) else v
            for k, v in row.items()
        }
        for row in results
    ]
    cursor.close()
    conn.close()
    return decoded_results


def update_student(student_id, subject_field, new_score):
    """更新学生成绩"""
    conn = connect_to_database()
    if not conn:
        return
    try:
        cursor = conn.cursor()
        sql = f"UPDATE students SET {subject_field} = %s WHERE student_id = %s"
        cursor.execute(sql, (new_score, student_id))
        conn.commit()
        print(f"{subject_field}成绩已更新")
    except Exception as e:
        print(f"更新成绩时出错: {e}")
    finally:
        cursor.close()
        conn.close()


def delete_student(student_id):
    """删除学生"""
    conn = connect_to_database()
    if not conn:
        return

    cursor = conn.cursor()
    sql = "DELETE FROM students WHERE student_id = %s"
    cursor.execute(sql, (student_id,))
    conn.commit()
    print("学生信息已删除")
    cursor.close()
    conn.close()

def get_class_averages():
    """根据指定科目计算班级平均分并排序"""
    print("请选择要计算平均分的科目: ")
    print("1. 语文")
    print("2. 数学")
    print("3. 英语")
    choice = input("请输入选项 (1/2/3): ")

    if choice == "1":
        subject_field = "chinese_score"
        subject_name = "语文"
    elif choice == "2":
        subject_field = "math_score"
        subject_name = "数学"
    elif choice == "3":
        subject_field = "english_score"
        subject_name = "英语"
    else:
        print("无效的选择! ")
        return

    conn = connect_to_database()
    if not conn:
        return

    try:
        cursor = conn.cursor(dictionary=True)
        sql = f"""
        SELECT class, AVG({subject_field}) AS average_score
        FROM students 
        GROUP BY class 
        ORDER BY average_score DESC;
        """
        cursor.execute(sql)
        results = cursor.fetchall()
        decoded_results = []
        for row in results:
            decoded_row = {}
            for key, value in row.items():
                if isinstance(value, (bytes, bytearray)):
                    decoded_row[key] = value.decode('utf-8')
                else:
                    decoded_row[key] = value
            decoded_results.append(decoded_row)

        print(f"\n[{subject_name}平均分（班级排名)]")
        for row in decoded_results:
            print(f"{row['class']}: {row['average_score']:.2f}")
        return decoded_results
    except Exception as e:
        print(f"查询出错: {e}")
    finally:
        cursor.close()
        conn.close()

def get_class_averages_all_subjects():
    """查询每个班级的语文、数学、英语平均分"""
    conn = connect_to_database()
    if not conn:
        return []

    try:
        cursor = conn.cursor(dictionary=True)
        sql = """
        SELECT 
            class,
            AVG(chinese_score) AS chinese_avg,
            AVG(math_score) AS math_avg,
            AVG(english_score) AS english_avg
        FROM students
        GROUP BY class
        ORDER BY class;
        """
        cursor.execute(sql)
        results = cursor.fetchall()

        decoded_results = []
        for row in results:
            decoded_row = {}
            for key, value in row.items():
                if isinstance(value, (bytes, bytearray)):
                    decoded_row[key] = value.decode('utf-8')
                else:
                    decoded_row[key] = value
            decoded_results.append(decoded_row)

        return decoded_results

    except Exception as e:
        print(f"查询出错: {e}")
        return []
    finally:
        cursor.close()
        conn.close()
